import { Pool } from 'pg'
import { withUserContext } from './rls-middleware'

// 创建 PostgreSQL 连接池
const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
})

// 测试数据库连接
pool.on('connect', () => {
    console.log('Connected to PostgreSQL database')
})

pool.on('error', (err) => {
    console.error('Unexpected error on idle client', err)
    process.exit(-1)
})

// 辅助函数：执行查询（带用户上下文）
async function query(text, params, userId = null) {
    if (userId) {
        return withUserContext(userId, async (client) => {
            return await client.query(text, params)
        })
    } else {
        // 传统查询方式（用于系统级操作）
        const client = await pool.connect()
        try {
            const result = await client.query(text, params)
            return result
        } finally {
            client.release()
        }
    }
}

// ==================== 产品相关函数 ====================

/**
 * 获取产品列表
 * @param {Object} options - 查询选项
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Array>} 产品列表
 */
export async function getProducts(options = {}, currentUserId = null) {
    const {
        page = 1,
        limit = 10,
        type = null,
        search = null,
        sortBy = 'created_at',
        sortOrder = 'desc',
        userId = null
    } = options

    let queryText = `
        SELECT p.*, u.id as user_id, u.username, u.full_name
        FROM products p
        LEFT JOIN users u ON p.user_id = u.id
        WHERE 1=1
    `
    const queryParams = []
    let paramCount = 0

    // 筛选条件
    if (type) {
        paramCount++
        queryText += ` AND p.type = $${paramCount}`
        queryParams.push(type)
    }

    if (userId) {
        paramCount++
        queryText += ` AND p.user_id = $${paramCount}`
        queryParams.push(userId)
    }

    if (search) {
        paramCount++
        queryText += ` AND (p.title ILIKE $${paramCount} OR p.description ILIKE $${paramCount})`
        queryParams.push(`%${search}%`)
    }

    // 排序
    const validSortFields = ['created_at', 'updated_at', 'title', 'view_count']
    const validSortOrders = ['asc', 'desc']

    if (validSortFields.includes(sortBy) && validSortOrders.includes(sortOrder)) {
        queryText += ` ORDER BY p.${sortBy} ${sortOrder.toUpperCase()}`
    } else {
        queryText += ` ORDER BY p.created_at DESC`
    }

    // 分页
    const offset = (page - 1) * limit
    paramCount++
    queryText += ` LIMIT $${paramCount}`
    queryParams.push(limit)

    paramCount++
    queryText += ` OFFSET $${paramCount}`
    queryParams.push(offset)

    const result = await query(queryText, queryParams, currentUserId)
    return result.rows
}

/**
 * 获取产品总数
 * @param {Object} options - 查询选项
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<number>} 产品总数
 */
export async function getProductsCount(options = {}, currentUserId = null) {
    const { type = null, search = null, userId = null } = options

    let queryText = 'SELECT COUNT(*) FROM products WHERE 1=1'
    const queryParams = []
    let paramCount = 0

    if (type) {
        paramCount++
        queryText += ` AND type = $${paramCount}`
        queryParams.push(type)
    }

    if (userId) {
        paramCount++
        queryText += ` AND user_id = $${paramCount}`
        queryParams.push(userId)
    }

    if (search) {
        paramCount++
        queryText += ` AND (title ILIKE $${paramCount} OR description ILIKE $${paramCount})`
        queryParams.push(`%${search}%`)
    }

    const result = await query(queryText, queryParams, currentUserId)
    return parseInt(result.rows[0].count)
}

/**
 * 根据 ID 获取产品
 * @param {string} id - 产品 ID
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object|null>} 产品数据
 */
export async function getProductById(id, currentUserId = null) {
    const queryText = `
        SELECT p.*, u.id as user_id, u.username, u.full_name, u.organization
        FROM products p
        LEFT JOIN users u ON p.user_id = u.id
        WHERE p.id = $1
    `

    const result = await query(queryText, [id], currentUserId)
    return result.rows[0] || null
}

/**
 * 创建产品
 * @param {Object} productData - 产品数据
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object>} 创建的产品
 */
export async function createProduct(productData, currentUserId = null) {
    const {
        title,
        description,
        type,
        url,
        file_path,
        thumbnail_url,
        user_id
    } = productData

    const queryText = `
        INSERT INTO products (title, description, type, url, file_path, thumbnail_url, user_id)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        RETURNING *
    `

    const queryParams = [title, description, type, url, file_path, thumbnail_url, user_id]
    const result = await query(queryText, queryParams, currentUserId)
    return result.rows[0]
}

/**
 * 更新产品
 * @param {string} id - 产品 ID
 * @param {Object} productData - 更新的产品数据
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object>} 更新后的产品
 */
export async function updateProduct(id, productData, currentUserId = null) {
    const {
        title,
        description,
        type,
        url,
        file_path,
        thumbnail_url
    } = productData

    const queryText = `
        UPDATE products 
        SET title = $1, description = $2, type = $3, url = $4, 
            file_path = $5, thumbnail_url = $6, updated_at = NOW()
        WHERE id = $7
        RETURNING *
    `

    const queryParams = [title, description, type, url, file_path, thumbnail_url, id]
    const result = await query(queryText, queryParams, currentUserId)
    return result.rows[0]
}

/**
 * 删除产品
 * @param {string} id - 产品 ID
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<boolean>} 是否删除成功
 */
export async function deleteProduct(id, currentUserId = null) {
    const queryText = 'DELETE FROM products WHERE id = $1'
    const result = await query(queryText, [id], currentUserId)
    return result.rowCount > 0
}

/**
 * 增加产品浏览量
 * @param {string} id - 产品 ID
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object>} 更新后的产品
 */
export async function incrementProductViewCount(id, currentUserId = null) {
    const queryText = `
        UPDATE products 
        SET view_count = view_count + 1, updated_at = NOW()
        WHERE id = $1
        RETURNING *
    `
    const result = await query(queryText, [id], currentUserId)
    return result.rows[0]
}

// ==================== 用户相关函数 ====================

/**
 * 根据用户名获取用户
 * @param {string} username - 用户名
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object|null>} 用户数据
 */
export async function getUserByUsername(username, currentUserId = null) {
    const queryText = 'SELECT * FROM users WHERE username = $1'
    const result = await query(queryText, [username], currentUserId)
    return result.rows[0] || null
}

/**
 * 根据邮箱获取用户
 * @param {string} email - 邮箱地址
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object|null>} 用户数据
 */
export async function getUserByEmail(email, currentUserId = null) {
    const queryText = 'SELECT * FROM users WHERE email = $1'
    const result = await query(queryText, [email.toLowerCase()], currentUserId)
    return result.rows[0] || null
}

/**
 * 根据 ID 获取用户
 * @param {string} id - 用户 ID
 * @returns {Promise<Object|null>} 用户数据
 */
export async function getUserById(id) {
    const queryText = 'SELECT * FROM users WHERE id = $1'
    const result = await query(queryText, [id])
    return result.rows[0] || null
}

/**
 * 创建用户
 * @param {Object} userData - 用户数据
 * @param {string} currentUserId - 当前用户ID（用于RLS）
 * @returns {Promise<Object>} 创建的用户
 */
export async function createUser(userData, currentUserId = null) {
    const {
        username,
        email,
        password_hash,
        full_name,
        organization,
        department,
        phone,
        application_reason
    } = userData

    const queryText = `
        INSERT INTO users (username, email, password_hash, full_name, organization, department, phone, application_reason)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        RETURNING *
    `

    const queryParams = [username, email, password_hash, full_name, organization, department, phone, application_reason]
    const result = await query(queryText, queryParams, currentUserId)
    return result.rows[0]
}

/**
 * 更新用户
 * @param {string} id - 用户 ID
 * @param {Object} userData - 更新的用户数据
 * @returns {Promise<Object>} 更新后的用户
 */
export async function updateUser(id, userData) {
    const fields = []
    const values = []
    let paramCount = 0

    // 动态构建更新字段
    Object.keys(userData).forEach(key => {
        if (userData[key] !== undefined) {
            paramCount++
            fields.push(`${key} = $${paramCount}`)
            values.push(userData[key])
        }
    })

    if (fields.length === 0) {
        throw new Error('No fields to update')
    }

    paramCount++
    const queryText = `
        UPDATE users 
        SET ${fields.join(', ')}, updated_at = NOW()
        WHERE id = $${paramCount}
        RETURNING *
    `

    values.push(id)
    const result = await query(queryText, values)
    return result.rows[0]
}

/**
 * 验证用户邮箱
 * @param {string} id - 用户 ID
 * @returns {Promise<Object>} 更新后的用户
 */
export async function verifyUserEmail(id) {
    return updateUser(id, { is_verified: true })
}

/**
 * 停用用户账户
 * @param {string} id - 用户 ID
 * @returns {Promise<Object>} 更新后的用户
 */
export async function deactivateUser(id) {
    return updateUser(id, { is_active: false })
}

/**
 * 激活用户账户
 * @param {string} id - 用户 ID
 * @returns {Promise<Object>} 更新后的用户
 */
export async function activateUser(id) {
    return updateUser(id, { is_active: true })
}

// 导出连接池以供其他模块使用
export { pool }